import os
import sys

# 添加当前文件所在目录到Python路径中
current_dir = os.path.dirname(os.path.abspath(__file__))
#sys.path.append(current_dir)

# 添加包的父目录到Python路径中
parent_dir = os.path.dirname(current_dir)
sys.path.append(parent_dir)

import pymysql
from mako.template import Template
from mako.exceptions import TemplateLookupException, SyntaxException
import public.jygt_coder_utitls as JygtCoderUtils
import public.jygt_coder_file as JygtCoderFile

print(JygtCoderUtils.get_entity_file_name('t_apple_bear'))
print(JygtCoderUtils.get_service_file_name('t_apple_bear'))

# 数据库连接配置
CONST_MYSQL_CONFIG = JygtCoderUtils.MySQLConfigurer(host='localhost',
                                                    user='root',
                                                    password='root1234',
                                                    database='db_jygt',
                                                    port=3306
                                                    )

"""CONST_MYSQL_CONFIG = JygtCoderUtils.MySQLConfigurer(host='119.45.213.49',
                                                    user='root',
                                                    password='root1234',
                                                    database='db_fkx',
                                                    port=63306
                                                    )"""
"""CONST_MYSQL_CONFIG = JygtCoderUtils.MySQLConfigurer(host='119.45.213.49',
                                                    user='root',
                                                    password='root1234',
                                                    database='db_dictation',
                                                    port=63306
                                                    )"""


connection = pymysql.connect(
    host=    CONST_MYSQL_CONFIG.getHost(),
    port=  CONST_MYSQL_CONFIG.getPort(),
    user=  CONST_MYSQL_CONFIG.getUser(),
    passwd=  CONST_MYSQL_CONFIG.getPassword(),
    database=  CONST_MYSQL_CONFIG.getDatabase()
)

# "mysql+pymysql://root:fkx%4020240604@172.16.0.3:63306/db_fkx"

import shutil

def delete_all_files_recursively(directory:str):
    if directory.find('dist') < 0 :
        return
    if os.path.exists(directory):
        shutil.rmtree(directory)  # 删除目录及其所有内容
        print(f"已删除: {directory} 及其所有文件和子目录。")
    else:
        print(f"目录 '{directory}' 不存在。")

def get_table_columns(database_name, table_name):
    print(f"get table colums in {database_name},{table_name}")
    try:
        with connection.cursor() as cursor:
            # 查询表结构信息的 SQL 语句
            query = """
            SHOW FULL COLUMNS FROM `{}`;
            """.format(table_name)

            # 执行查询
            cursor.execute(query)
            columns = cursor.fetchall()

            column_info = []
            for column in columns:
                type = get_sqlalchemy_type(column[1])
                column_info.append({
                    'name': column[0],
                    'type': get_sqlalchemy_type(column[1]),
                    'nullable': 'nullable=True' if column[3] == 'YES' else 'nullable=False',
                    'default': 'default=func.now' if column[5] == 'CURRENT_TIMESTAMP' else  (f"default='{column[5]}'" if ((type.startswith("String") or (type.startswith("Date") or True )) and column[5] != None) else f"default={column[5]}"),
                    'comment': f"'{column[6]}'",
                    'primary_key': 'primary_key=True,' if column[4] == 'PRI' else ''
                })
            return column_info
    finally:
        pass

def get_length(mysql_type):
    """提取 MySQL 类型中的长度（如果适用）。"""
    if '(' in mysql_type:
        return mysql_type[mysql_type.index('(') + 1: mysql_type.index(')')]
    return None

def get_sqlalchemy_type(mysql_type):
    if mysql_type.startswith('int'):
        return 'Integer'
    elif mysql_type.startswith('varchar'):
        return 'String({})'.format(get_length(mysql_type) or 255)  # 默认长度为 255
    elif mysql_type.startswith('char'):
        return 'String({})'.format(get_length(mysql_type) or 1)  # 默认长度为 1
    elif mysql_type.startswith('text'):
        return 'Text'
    elif mysql_type.startswith('blob'):
        return 'LargeBinary'
    elif mysql_type.startswith('datetime'):
        return 'DateTime'
    elif mysql_type.startswith('timestamp'):
        return 'DateTime'
    elif mysql_type.startswith('date'):
        return 'Date'
    elif mysql_type.startswith('time'):
        return 'Time'
    elif mysql_type.startswith('enum'):
        return 'Enum'  # Enum 需要特别处理
    elif mysql_type.startswith('set'):
        return 'Enum'  # Set 也可以视为 Enum
    elif mysql_type.startswith('decimal'):
        return 'Numeric({}, {})'.format(get_length(mysql_type).split(',')[0], get_length(mysql_type).split(',')[1])  # 提取精度和小数位
    elif mysql_type.startswith('float'):
        return 'Float'
    elif mysql_type.startswith('double'):
        return 'Float'  # SQLAlchemy 将 float 和 double 都映射为 Float
    elif mysql_type.startswith('bit'):
        return 'Boolean'
    else:
        return 'String(255)'  # 默认类型
    
from mako.lookup import TemplateLookup

# 指定模板目录
# template_dir = './template/'
# 定义预处理器函数
import re
def remove_empty_lines(template_text):
    # 使用正则表达式去除空行
    return template_text
    return re.sub(r'^\s*$', '', template_text, flags=re.MULTILINE)

import os
current_dir = os.path.dirname(__file__)  # 获取当前文件的目录
lookup = TemplateLookup(directories=[
    os.path.join(current_dir, 'template'), 
    os.path.join(current_dir, '..', 'template')])
#lookup = TemplateLookup(directories=['./template/','../template/'])

def get_table_indexes(database_name, table_name):
    print(f"get table indexes in {database_name},{table_name}")
    try:
        with connection.cursor() as cursor:
            # 查询表索引信息的 SQL 语句
            query = """
            SHOW INDEX FROM `{}`;
            """.format(table_name)

            # 执行查询
            cursor.execute(query)
            indexes = cursor.fetchall()

            index_info = {}
            for index in indexes:
                index_name = index[2]
                column_name = index[4]
                non_unique = index[1]

                if index_name not in index_info:
                    index_info[index_name] = {'columns': [], 'unique': non_unique == 0}

                index_info[index_name]['columns'].append(column_name)

            return index_info
    finally:
        pass

def generate_connection_code(objMySQLConfigurer:JygtCoderUtils.MySQLConfigurer):
  try:
    template = lookup.get_template('connection.mako')
    return template.render(conn_str=objMySQLConfigurer.getSQLAlchemyConnectionString())

  except TemplateLookupException as e:
      print(f"Template not found: {e}")
      return ""

  except SyntaxException as e:
      print(f"Syntax error in template: {e}")
      return ""

  except Exception as e:
      print(f"An error occurred: {e}")
      return ""
def generate_model_code(table_name, columns):
  try:
    template = lookup.get_template('entity.mako')
    return template.render(table_name=table_name, columns=columns)

  except TemplateLookupException as e:
      print(f"Template not found: {e}")
      return ""

  except SyntaxException as e:
      print(f"Syntax error in template: {e}")
      return ""

  except Exception as e:
      print(f"An error occurred: {e}")
      return ""



def generate_service_code(table_name, columns,indexes):
    try:
        template = lookup.get_template('service.mako')
        return template.render(table_name=table_name, columns=columns,indexes=indexes)
    
    except TemplateLookupException as e:
        print(f"Template not found: {e}")
        return ""

    except SyntaxException as e:
        print(f"Syntax error in template: {e}")
        return ""

    except Exception as e:
        print(f"An error occurred: {e}")
        return ""

def generate_test_code(table_name, columns,indexes):
    try:
        template = lookup.get_template('test.mako')
        return template.render(table_name=table_name, columns=columns,indexes=indexes)
    
    except TemplateLookupException as e:
        print(f"Template not found: {e}")
        return ""

    except SyntaxException as e:
        print(f"Syntax error in template: {e}")
        return ""

    except Exception as e:
        print(f"An error occurred: {e}")
        return ""

def save_code_to_file(code, filename, directory):
    if not os.path.exists(directory):
        os.makedirs(directory)
    with open(os.path.join(directory, filename), "w",encoding='utf-8') as f:
        f.write(code)

try:
    
    dist_directory = os.path.join(current_dir,'../../dat/','dist', CONST_MYSQL_CONFIG.getDatabase())
    delete_all_files_recursively(dist_directory)

    connction_code = generate_connection_code(CONST_MYSQL_CONFIG)
      
    dist_directory_service = os.path.join(current_dir,'../../dat/','dist', JygtCoderUtils.get_connection_file_directory(CONST_MYSQL_CONFIG.getDatabase()))
            
    save_code_to_file(connction_code, JygtCoderUtils.get_connection_file_name(),dist_directory_service)

    # copy 
    srcDir = os.path.join(parent_dir, 'public', )
    dstDir = os.path.join(parent_dir, '..','dat','dist',CONST_MYSQL_CONFIG.getDatabase(), 'public')

    JygtCoderFile.copy(srcDir,dstDir)

    with connection.cursor() as cursor:
        # 获取所有表
        cursor.execute("SHOW TABLES")
        tables = cursor.fetchall()

        for (table_name,) in tables:
            print(f"表名: {table_name}")

            # 获取表的字段
            columns = get_table_columns(CONST_MYSQL_CONFIG.getDatabase(), table_name)

            # 生成模型代码
            model_code = generate_model_code(table_name, columns)
            print(f"$entiycode:\r\n{model_code}\r\nend")

             # 获取表的索引
            indexes = get_table_indexes(CONST_MYSQL_CONFIG.getDatabase(), table_name)

            # 生成服务代码
            service_code = generate_service_code(table_name, columns,indexes)
            print(service_code)

            
            # 生成测试代码
            test_code = generate_test_code(table_name, columns,indexes)
            print(test_code)

            # 保存生成的代码到文件
            dist_directory_entity = os.path.join(current_dir,'../../dat/','dist', JygtCoderUtils.get_entity_file_directory(table_name,CONST_MYSQL_CONFIG.getDatabase()))
            save_code_to_file(model_code,JygtCoderUtils.get_entity_file_name(table_name), dist_directory_entity)
            
            dist_directory_service = os.path.join(current_dir,'../../dat/','dist', JygtCoderUtils.get_service_file_directory(table_name,CONST_MYSQL_CONFIG.getDatabase()))
            save_code_to_file(service_code,JygtCoderUtils.get_service_file_name(table_name), dist_directory_service)

            dist_directory_test = os.path.join(current_dir,'../../dat/','dist', JygtCoderUtils.get_test_file_directory(table_name,CONST_MYSQL_CONFIG.getDatabase()))
            save_code_to_file(test_code,JygtCoderUtils.get_test_file_name(table_name), dist_directory_test)

finally:
    connection.close()